﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad

Public Class DocumentoClienteBultoDatos

    Private Path As String = "H:\DATOS\PRUEBA\"

    Function generarCodigoBulto() As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) + 1 AS CONTAR FROM Documento_Cliente_Bulto"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("CONTAR").ToString.Trim)
    End Function

    Sub registrarDocumentoBulto(bulto As DocumentoBulto)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Cliente_Bulto(CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo,DCB_Codigo, " & _
                              "TCA_Caja,DCB_Cantidad,DCB_PesoNeto,DCB_PesoBruto,DCB_Caja,DCB_Precinto,DCB_Observacion, " & _
                              "DCB_FechaInicio,DCB_FechaFin,DCB_Activo) " & _
                              "VALUES(@CIA,@SUC,@TDOC,@DCICOD,@DCBCOD,@TCACAJA,@DCBCAN,@PESONET,@PESOBRUT, " & _
                              "@DCBCAJA,@DCBPREC,@DCBOBSER,@DCBFECHINI,GETDATE(),'1')"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", bulto.CIA)
            .Parameters.AddWithValue("@SUC", bulto.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", bulto.TDOC_Codigo)
            .Parameters.AddWithValue("@DCICOD", bulto.DCI_Codigo)
            .Parameters.AddWithValue("@DCBCOD", bulto.DCB_Codigo)
            .Parameters.AddWithValue("@TCACAJA", bulto.TCA_Caja)
            .Parameters.AddWithValue("@DCBCAN", bulto.DCB_Cantidad)
            .Parameters.AddWithValue("@PESONET", bulto.DCB_PesoNeto)
            .Parameters.AddWithValue("@PESOBRUT", bulto.DCB_PesoBruto)
            .Parameters.AddWithValue("@DCBCAJA", bulto.DCB_Caja)
            .Parameters.AddWithValue("@DCBPREC", bulto.DCB_Precinto)
            .Parameters.AddWithValue("@DCBOBSER", bulto.DCB_Observacion)
            .Parameters.AddWithValue("@DCBFECHINI", bulto.DCB_FechaInicio)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarBultosRegistrados(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcb.DCB_Codigo,dcb.DCB_Cantidad,dcb.DCB_PesoBruto,dcb.DCB_PesoNeto,dcb.DCB_Observacion " &
                              "FROM Documento_Cliente_Bulto dcb " &
                              "INNER JOIN  Documento_Cliente dc " &
                              "ON dc.DCI_Codigo = dcb.DCI_Codigo AND dc.DCI_Programado = '0' " &
                              "WHERE dcb.DCI_Codigo = '" & codPedido & "' AND dcb.DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub desactivarBultos(codBulto As Integer)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Bulto " &
                              "SET DCB_Activo = '0' " &
                              "WHERE DCB_Codigo = @codigo AND DCB_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@codigo", codBulto)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerBulto(codigo As Integer) As DocumentoBulto
        Dim db As DocumentoBulto = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT TCA_Caja,DCB_Cantidad,DCB_Caja,DCB_Precinto,DCB_PesoNeto,DCB_PesoBruto,DCB_Observacion " &
                              "FROM Documento_Cliente_Bulto " &
                              "WHERE DCB_Codigo = " & codigo & " AND DCB_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            Dim tipoCaja As String = ds.Tables(0).Rows(0)("TCA_Caja").ToString.Trim
            Dim cantidad As String = ds.Tables(0).Rows(0)("DCB_Cantidad").ToString.Trim
            Dim caja As String = ds.Tables(0).Rows(0)("DCB_Caja").ToString.Trim
            Dim precinto As String = ds.Tables(0).Rows(0)("DCB_Precinto").ToString.Trim
            Dim neto As Decimal = CDec(ds.Tables(0).Rows(0)("DCB_PesoNeto").ToString.Trim)
            Dim bruto As Decimal = CDec(ds.Tables(0).Rows(0)("DCB_PesoBruto").ToString.Trim)
            Dim observacion As String = ds.Tables(0).Rows(0)("DCB_Observacion").ToString.Trim

            db = New DocumentoBulto(Nothing, Nothing, Nothing, Nothing, Nothing, tipoCaja, cantidad, neto, bruto, caja, precinto, observacion, Nothing)
        End If

        Return db
    End Function

    Sub actualizarDocumentoBulto(bulto As DocumentoBulto)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Bulto " &
                              "SET TCA_Caja = @tcaja, DCB_Cantidad = @cantBulto, " &
                              "DCB_PesoNeto = @pesoNeto, DCB_PesoBruto = @pesoBruto, " &
                              "DCB_Caja = @caja, DCB_Precinto = @precinto, " &
                              "DCB_Observacion = @observacion " &
                              "WHERE DCB_Codigo = @codigo AND DCB_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@tcaja", bulto.TCA_Caja)
            .Parameters.AddWithValue("@cantBulto", bulto.DCB_Cantidad)
            .Parameters.AddWithValue("@pesoNeto", bulto.DCB_PesoNeto)
            .Parameters.AddWithValue("@pesoBruto", bulto.DCB_PesoBruto)
            .Parameters.AddWithValue("@caja", bulto.DCB_Caja)
            .Parameters.AddWithValue("@precinto", bulto.DCB_Precinto)
            .Parameters.AddWithValue("@observacion", bulto.DCB_Observacion)
            .Parameters.AddWithValue("@codigo", bulto.DCB_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarBultosXPedido(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcb.DCB_Codigo,dcb.DCB_Cantidad, dcb.DCB_PesoNeto, dcb.DCB_PesoBruto, tc.TCA_Descripcion, dcb.DCB_Observacion " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo " &
                              "AND dcb.TDOC_Codigo = dc.TDOC_Codigo AND dcb.DCI_Codigo = dc.DCI_Codigo " &
                              "AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN Tipo_Caja tc " &
                              "ON tc.TCA_Codigo = dcb.TCA_Caja AND tc.TCA_Activo = '1' " &
                              "WHERE dc.DCI_Codigo = '" & codPedido & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarArticulosLargos(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT a.ART_Codigo, a.ART_Descripcion, um.UNM_Abreviatura, da.DADPUB_Cantidad " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN VW_VistaDocumentoAlmacen da " &
                              "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = da.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "WHERE dcad.CIA_Cliente = '01' AND dcad.SUC_Cliente = '01' AND dcad.TDOC_Cliente = 'P' " &
                              "AND dcad.DCI_Codigo = '0W01508' AND dcad.DCAD_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerRutaCliente(codPedido As String) As String
        Dim ruta As String = "Z1"
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT r.RUT_Descripcion " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Ruta r " &
                              "ON r.RUT_Codigo = dc.RUT_Codigo " &
                              "WHERE dc.DCI_Codigo = '" & codPedido & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()


        If ds.Tables(0).Rows.Count <> 0 Then
            ruta = ds.Tables(0).Rows(0)("RUT_Descripcion").ToString.Trim
        End If

        Return ruta
    End Function

    Sub registrarCTRG01(gen As Generico)
        Dim query As String = "INSERT INTO CTRG01(DOCTO,ID,BINICIAL,BFINAL,BULTOS,PNETO,PBRUTO,CAJAS,OBSERVA) VALUES('" & gen.DOCTO & "'," & _
                              gen.ID & "," & gen.BINICIAL & "," & gen.BFINAL & "," & gen.BULTOS & "," & gen.PNETO & "," & gen.PBRUTO & ",'" & _
                              gen.CAJAS & "','" & gen.OBSERVA & "')"
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG01.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Sub actualizarCTRG01(gen As Generico)
        Dim query As String = "UPDATE CTRG01 SET BULTOS = " & gen.BULTOS & ", PNETO = " & gen.PNETO & ", " & _
                              "PBRUTO = " & gen.PBRUTO & ", CAJAS = '" & gen.CAJAS & "', OBSERVA = '" & gen.OBSERVA & "' " & _
                              "WHERE DOCTO = '" & gen.DOCTO & "' AND ID = " & gen.ID

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG01.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Sub eliminarCTRG01(gen As Generico)
        Dim query As String = "DELETE FROM CTRG01 WHERE DOCTO = '" & gen.DOCTO & "' AND ID = " & gen.ID

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG01.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Function obtenerImpresionBultos(codPedido As String, tipDocumento As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT c.CLI_Marca, r.RUT_Descripcion, dc.DCI_Codigo, " &
                              "at.AGE_Descripcion, dcb.DCB_PesoBruto, dcb.DCB_Cantidad, " &
                              "'*'+RTRIM(dc.DCI_Codigo)+'.' AS Barra, " &
                              "dcb.DCB_Codigo, ce.CLEN_Direccion, " &
                              "dbo.FNX_ObtenerAgrupado(LTRIM(RTRIM(d.DIS_Descripcion)), LTRIM(RTRIM(p.PRO_Descripcion)), LTRIM(RTRIM(de.DEP_Descripcion))) AS Ubigeo, " &
                              "c.CLI_Descripcion " &
                              "FROM Documento_Cliente dc " &
                              "LEFT JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "LEFT JOIN Ruta r " &
                              "ON r.RUT_Codigo = dc.RUT_Codigo " &
                              "LEFT JOIN AgenciaTransporte at " &
                              "ON at.AGE_Codigo = dc.AGE_Codigo AND at.AGE_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo " &
                              "AND dcb.TDOC_Codigo = dc.TDOC_Codigo AND dcb.DCI_Codigo = dc.DCI_Codigo " &
                              "AND dcb.DCB_Activo = '1' " &
                              "LEFT JOIN CLienteEntrega ce " &
                              "ON ce.CLEN_Codigo = dc.CLEN_Codigo AND ce.CLI_Codigo = dc.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito d " &
                              "ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "LEFT JOIN Provincia p " &
                              "ON p.PRO_Codigo = d.PRO_Codigo AND p.PRO_Activo = '1' " &
                              "LEFT JOIN Departamento de " &
                              "ON de.DEP_Codigo = p.DEP_Codigo AND de.DEP_Activo = '1' " &
                              "WHERE dc.DCI_Codigo = @DCI AND dc.TDOC_Codigo = @TDO"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@TDO", tipDocumento)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class
